package com.eascs.admin.classify.dao;

import org.springframework.stereotype.Repository;

import com.eascs.admin.classify.entity.O2oLoanAbjustForm;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;

@Repository
public class O2oClassifyDao extends BaseO2oDao{
	
	public PageResult<Object[]> queryByPageAuto(O2oLoanAbjustForm abjustForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, abjustForm.getPageSize(), abjustForm.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("select * from ((SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,oor.disposaldate, ");
		querySql.append(" case  when olat.flow_status='CLO'  or olat.flow_status='' then olat.new_loan_type else olat.old_loan_type end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date,olat.flow_status,olat.create_time ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t1 where t1.defaultamount is not null order by t1.disposaldate DESC limit 0,99999) ");
		querySql.append(" union all (SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,oor.disposaldate, ");
		querySql.append(" case when olat.flow_status='CLO'  or olat.flow_status='' then olat.new_loan_type else olat.old_loan_type end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date,olat.flow_status,olat.create_time ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t2 where t2.defaultamount is null and t2.pay_amount is not null order by t2.pay_date DESC limit 0,99999) ");
		querySql.append(" union all (SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,oor.disposaldate, ");
		querySql.append(" case when olat.flow_status='CLO'  or olat.flow_status='' then olat.new_loan_type else olat.old_loan_type end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date,olat.flow_status,olat.create_time ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t3 where t3.defaultamount is null and t3.pay_amount is null order by t3.loan_end_date DESC limit 0,99999)) t where 1=1 ");
		if(StringUtil.isNotBlank(abjustForm.getLoanType())){
			querySql.append(" and t.new_loan_type = ? ");
			pageQuery.addParam(abjustForm.getLoanType());
		}
		if(StringUtil.isNotBlank(abjustForm.getProvincialArea())){
			querySql.append(" and t.province_name like ? ");
			pageQuery.addLikeParam(abjustForm.getProvincialArea());
		}
		if(StringUtil.isNotBlank(abjustForm.getOpererName())){
			querySql.append(" and t.operer_name like ? ");
			pageQuery.addLikeParam(abjustForm.getOpererName());
		}
		if(StringUtil.isNotBlank(abjustForm.getOperationPlatform())){
			querySql.append(" and t.ltd_name like ? ");
			pageQuery.addLikeParam(abjustForm.getOperationPlatform());
		}
		if(StringUtil.isNotBlank(abjustForm.getBillCode())){
			querySql.append(" and t.bill_code like ? ");
			pageQuery.addLikeParam(abjustForm.getBillCode());
		}
		if(StringUtil.isNotBlank(abjustForm.getLoanUser())){
			querySql.append(" and t.loan_user_name like ? ");
			pageQuery.addLikeParam(abjustForm.getLoanUser());
		}
		pageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	/**
	 * 提供下载功能，不同的sql语句
	 * @param abjustForm
	 * @param download
	 * @return
	 * @author hx
	 */
	public PageResult<Object[]> queryByPageAuto(O2oLoanAbjustForm abjustForm ,String download) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, abjustForm.getPageSize(), abjustForm.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("select * from ((SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,DATE_FORMAT(oor.disposaldate,'%Y-%m-%d') as disposaldate, ");
		querySql.append(" case  when olat.flow_status='CLO'  or olat.flow_status='' then if(olat.new_loan_type='NORMAL_LOAN','正常贷款','关注贷款') else if(olat.old_loan_type='NORMAL_LOAN','正常贷款','关注贷款') end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t1 where t1.defaultamount is not null order by t1.disposaldate DESC limit 0,99999) ");
		querySql.append(" union all (SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,DATE_FORMAT(oor.disposaldate,'%Y-%m-%d') as disposaldate, ");
		querySql.append(" case when olat.flow_status='CLO'  or olat.flow_status='' then if(olat.new_loan_type='NORMAL_LOAN','正常贷款','关注贷款') else if(olat.old_loan_type='NORMAL_LOAN','正常贷款','关注贷款') end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t2 where t2.defaultamount is null and t2.pay_amount is not null order by t2.pay_date DESC limit 0,99999) ");
		querySql.append(" union all (SELECT * FROM (SELECT olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name,olat.loan_amount, ");
		querySql.append(" olat.pay_amount,DATE_FORMAT(oeld.pay_date,'%Y-%m-%d') as pay_date,olat.payment_amount,olat.defaultamount,DATE_FORMAT(oor.disposaldate,'%Y-%m-%d') as disposaldate, ");
		querySql.append(" case when olat.flow_status='CLO'  or olat.flow_status='' then if(olat.new_loan_type='NORMAL_LOAN','正常贷款','关注贷款') else if(olat.old_loan_type='NORMAL_LOAN','正常贷款','关注贷款') end as new_loan_type, ");
		querySql.append(" DATE_FORMAT(ADDDATE(obld.loan_date,INTERVAL obld.loan_term  MONTH),'%Y-%m-%d') as loan_end_date ");
		querySql.append(" from o2o_loan_abjust_type olat left join o2o_overdue_report oor on olat.bill_code=oor.billcode ");
		querySql.append(" left join o2o_ea_loan_data oeld on olat.bill_code=oeld.bill_code left join o2o_bank_loan_data obld on olat.bill_code=obld.bill_code ");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.is_delete='N' ) t3 where t3.defaultamount is null and t3.pay_amount is null order by t3.loan_end_date DESC limit 0,99999)) t where 1=1 ");
		if(StringUtil.isNotBlank(abjustForm.getLoanType())){
			querySql.append(" and t.new_loan_type = ? ");
			pageQuery.addParam(abjustForm.getLoanType());
		}
		if(StringUtil.isNotBlank(abjustForm.getProvincialArea())){
			querySql.append(" and t.province_name like ? ");
			pageQuery.addLikeParam(abjustForm.getProvincialArea());
		}
		if(StringUtil.isNotBlank(abjustForm.getOpererName())){
			querySql.append(" and t.operer_name like ? ");
			pageQuery.addLikeParam(abjustForm.getOpererName());
		}
		if(StringUtil.isNotBlank(abjustForm.getOperationPlatform())){
			querySql.append(" and t.ltd_name like ? ");
			pageQuery.addLikeParam(abjustForm.getOperationPlatform());
		}
		if(StringUtil.isNotBlank(abjustForm.getBillCode())){
			querySql.append(" and t.bill_code like ? ");
			pageQuery.addLikeParam(abjustForm.getBillCode());
		}
		if(StringUtil.isNotBlank(abjustForm.getLoanUser())){
			querySql.append(" and t.loan_user_name like ? ");
			pageQuery.addLikeParam(abjustForm.getLoanUser());
		}
		pageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	
	public PageResult<Object[]> queryByPageMamuil(O2oLoanAbjustForm abjustForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, abjustForm.getPageSize(), abjustForm.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT olatf.id,olat.bill_code,olat.loan_user_name,olat.operer_name,olat.ltd_name,olat.province_name");
		querySql.append(",olatf.old_loan_type,olatf.new_loan_type,olatf.flow_status,olatf.create_user_name,olatf.create_time");
		querySql.append(" from o2o_loan_abjust_type_flow olatf LEFT JOIN o2o_loan_abjust_type olat on olatf.abjust_id =olat.id");
		querySql.append(" where /**DATA_PERMISSION(olat.LTD)**/ olat.data_type='MA' and olatf.is_delete='N' ");
		if(StringUtil.isNotBlank(abjustForm.getLoanType())){
			querySql.append(" and olatf.new_loan_type = ? ");
			pageQuery.addParam(abjustForm.getLoanType());
		}
		if(StringUtil.isNotBlank(abjustForm.getOperationPlatform())){
			querySql.append(" and olat.ltd_name like ? ");
			pageQuery.addLikeParam(abjustForm.getOperationPlatform());
		}
		if(StringUtil.isNotBlank(abjustForm.getCreateUserName())){
			querySql.append(" and olatf.create_user_name like ? ");
			pageQuery.addLikeParam(abjustForm.getCreateUserName());
		}
		if(StringUtil.isNotBlank(abjustForm.getFlowStatus())){
			querySql.append(" and olatf.flow_status = ? ");
			pageQuery.addParam(abjustForm.getFlowStatus());
		}
		if(StringUtil.isNotBlank(abjustForm.getBillCode())){
			querySql.append(" and olat.bill_code like ? ");
			pageQuery.addLikeParam(abjustForm.getBillCode());
		}
		if(StringUtil.isNotBlank(abjustForm.getCreateTimeStart())){
			querySql.append(" and olatf.create_time >= ? ");
			pageQuery.addParam(abjustForm.getCreateTimeStart());
		}
		if(StringUtil.isNotBlank(abjustForm.getCreateTimeEnd())){
			querySql.append(" and olatf.create_time < ? ");
			pageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(abjustForm.getCreateTimeEnd()), 1)));
		}
		if(StringUtil.isNotBlank(abjustForm.getLoanUser())){
			querySql.append(" and olatf.loan_user_name like ? ");
			pageQuery.addLikeParam(abjustForm.getLoanUser());
		}
		querySql.append(" order by olatf.create_time asc ");
		pageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(pageQuery);
	}
}
